Index Design for Enforcing Partial Referential Integrity Efficiently

Referential integrity is fundamental for data processing and data quality. The SQL standard proposes di↵erent semantics under which referential integrity can be enforced in practice. Under simple semantics, only total foreign key values must be matched by some referenced key values. Under partial semantics, total and partial foreign key values must be matched by some referenced key values. Support for simple semantics is extensive and widespread across di↵erent database management systems but, surprisingly, partial semantics does not enjoy any native support in any known systems. Previous research has left open the questions whether partial referential integrity is useful for any real-world applications and whether it can enjoy e cient support at the systems level. As our first contribution we show that e cient support for partial referential integrity can provide database users with intelligent query and update services. Indeed, we regard partial semantics as an e↵ective imputation technique for missing data in query answers and update operations, which increases the quality of these services. As our second contribution we show how partial referential integrity can be enforced e ciently for real-world foreign keys. For that purpose we propose triggers and exploit di↵erent index structures. Our experiments with synthetic and benchmark data sets confirm that our index structures do not only boost the performance of the state-of-the-art recommendation for enforcing partial semantics in real-world foreign keys, but show trends that are similar to enforcing simple semantics.